﻿using MySql.Data.MySqlClient;
using System;
using System.Data;
using System.Diagnostics;
using System.Windows.Forms;

namespace nhCommon
{
    /// <summary>
    /// 访问MySql的类
    /// </summary>
    public class SlMySql
    {
        private string ConStr { get; set; }

        /// <summary>
        /// 返回的数据适配器
        /// </summary>
        private MySqlDataAdapter Adapter { get; set; }

        /// <summary>
        /// 返回的数据集
        /// </summary>
        public DataSet DataSet { get; private set; }

        public SlMySql(string conStr)
        {
            ConStr = conStr;
        }

        /// <summary>
        /// 显示表，可调UpdataTable修改
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public BindingSource ShowTable(string sql)
        {
            using (MySqlConnection con = new MySqlConnection(ConStr))
            {
                _ = new MySqlCommand
                {
                    CommandText = sql
                };
                Adapter = new MySqlDataAdapter(sql, con);

                DataSet = new DataSet();
                Adapter.Fill(DataSet);
                BindingSource bs = new BindingSource()
                {
                    DataSource = DataSet.Tables[0],
                };
                return bs;
            }
        }

        /// <summary>
        /// 更新表格
        /// </summary>
        /// <returns>更新成功行数，-1为失败</returns>
        public int UpdataTable()
        {
            //创建命令重建对象
            _ = new MySqlCommandBuilder(Adapter);

            try //更新数据
            {
                return Adapter.Update(DataSet); //这里是关键
            }
            catch (Exception ex)
            {
                MessageBox.Show($"表格中修改或新增的记录错误：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return -1;
            }
        }

        /// <summary>
        /// 从数据库读取表
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable GetTable(string sql)
        {
            using (MySqlConnection con = new MySqlConnection(ConStr))
            {
                DataTable table = null;
                try
                {
                    DataSet dst = new DataSet();
                    MySqlDataAdapter adapter = new MySqlDataAdapter(sql, con);
                    adapter.Fill(dst);
                    table = dst.Tables[0];
                }
                catch (Exception ex)
                {
                    MessageBox.Show(string.Format("[数据库]关闭数据库连接异常:{0}{1}",
                        ex.Message.ToString(), ex.StackTrace.ToString()),
                        "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                return table;
            }
        }

        /// <summary>
        /// 增加或更新表           
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>返回影响数据库的行数，异常返回-1</returns>
        public int ExecuteSQL(string sql)
        {
            int ret = -1;
            using (MySqlConnection con = new MySqlConnection(ConStr))
            {
                try
                {
                    con.Open();
                    MySqlCommand myCmd = new MySqlCommand(sql, con);
                    ret = myCmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    Debug.WriteLine(ex.Message);
                }
                finally
                {
                    con.Close();
                }
            }
            return ret;
        }
    }
}
